﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VRDD.InputDto._20220314;
using HCQ2_Model.VRDD.InputDto._211217;
using HCQ2_Model.VRDD.OutputDto._20220314;
using HCQ2_Model.VRDD.OutputDto._211217;
using HCQ2_Model.VRQDMeta.InputDto._20220112;
using HCQ2_Model.VRQDMeta.OutputDto._20220112;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDEventDAL: IT_VRDDEventDAL
    {
        /// <summary>
        ///  按日期删除事件提醒
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int DeleteMemoRemindByDate(InputDeleteMemoAlert param)
        {
            sb?.Clear();
            sb.Append($@"DELETE FROM T_VRDDEventAlert WHERE userid='{param.userID}' AND memoId={param.memoID} AND memoDate>='{param.date}';");
            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }
        /// <summary>
        ///  获取事件列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputMemoList> GetMemoList(InputMemoList param)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM 
            (SELECT e.*,a.memoRemindID,a.indexRemind,a.Type,a.Date,a.Time,a.colorNum,ROW_NUMBER() OVER(ORDER BY a.Date,a.Time) AS num FROM 
            (SELECT memoId,memoRemindID,indexRemind,aType AS Type,memoDate AS Date,remindDate AS Time,colorNum FROM T_VRDDEventAlert 
            WHERE userid='{param.userID}' ");
            if (param.type > -1)
                sb.Append($" AND indexRemind=1 ");//AND remindDate>=LEFT(CONVERT(Time(0),getdate(),2),5)
            sb.Append($@")a INNER JOIN
            (SELECT memoId AS memoID,memo,imageUrl AS Image,audioUrl AS Audio,videoUrl AS Video 
            FROM T_VRDDEvent WHERE userid='{param.userID}' AND CONVERT(varchar(100),createDate,23)='{param.date}' )e ON a.memoId=e.memoId)m WHERE m.num>{(param.page-1)*param.rows}");
            return SqlHelper.ExecuteList<OutputMemoList>(sb.ToString());
        }
        /// <summary>
        ///  获取事件提醒列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputMemoRemindList> GetMemoRemindList(InputMemoRemindList param,out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT e.*,a.memoRemindID,a.indexRemind,a.Type,a.Date,a.Time,a.colorNum,ROW_NUMBER() OVER(ORDER BY a.Date,a.Time) AS num FROM 
            (SELECT memoId,memoRemindID,indexRemind,aType AS Type,memoDate AS Date,remindDate AS Time,colorNum FROM T_VRDDEventAlert  WHERE userid='{param.userID}' AND memoDate='{param.date}' ");
            if (param.type > -1)
                sb.Append($" AND indexRemind=1 ");
            sb.Append($@")a INNER JOIN
            (SELECT memoId AS memoID,memo,imageUrl AS Image,audioUrl AS Audio,videoUrl AS Video 
            FROM T_VRDDEvent WHERE userid='{param.userID}')e ON a.memoId=e.memoId)m ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM {sb.ToString()}"));
            return SqlHelper.ExecuteList<OutputMemoRemindList>($"SELECT TOP {param.rows} * FROM " + sb.ToString() + $" WHERE m.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取事件日期列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputMemoDateList> GetMemoDateList(InputMemoDateList param)
        {
            sb?.Clear();
            //获取事件日期列表
            //sb.Append($@"SELECT n.Date,CAST(SUM(n.haveRecord) AS BIT)AS haveRecord FROM 
            //(SELECT CONVERT(varchar(100),createDate,23)AS Date,
            //haveRecord=(SELECT COUNT(0) FROM T_VRDDEventAlert WHERE memoId=e.memoId)
            //FROM T_VRDDEvent e WHERE e.userid='{param.userID}' AND CONVERT(varchar(100),e.createDate,23)>='{param.dateStart}' 
            //AND CONVERT(varchar(100),e.createDate,23)<='{param.dateEnd}')n GROUP BY n.Date ORDER BY n.Date ASC;");

            //获取事件提醒日期列表
            sb.Append($@"SELECT n.Date,CAST(SUM(n.haveRecord) AS BIT)AS haveRecord FROM 
            (SELECT memoDate AS Date,
            haveRecord=(SELECT COUNT(0) FROM T_VRDDEventAlert WHERE memoId=e.memoId AND indexRemind=1)
            FROM T_VRDDEventAlert e WHERE e.userid='{param.userID}' AND memoDate>='{param.dateStart}' AND 
            memoDate<='{param.dateEnd}')n GROUP BY n.Date ORDER BY n.Date ASC;");
            return SqlHelper.ExecuteList<OutputMemoDateList>(sb.ToString());
        }

        /// <summary>
        ///  获取应用商品列表
        ///  create_id：【是否点赞、收藏、购买】
        /// </summary>
        /// <returns></returns>
        public List<OutputMetaResult> GetMetaResult(InputMetaAppList param,int user_id, int create_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM (SELECT info.*,rel.folderID,d.folderName,d.bsType,ROW_NUMBER() 
            OVER(ORDER BY info.updateTime DESC)AS num FROM 
            (SELECT file_id,folder_id AS folderID FROM dbo.T_DocumentFolderRelation r WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" AND folder_path LIKE '{param.folderPath}%' ");
            sb.Append($@")rel INNER JOIN (SELECT folder_id,folder_name AS folderName,bs_type AS bsType FROM dbo.T_DocumentFolder)d 
            ON rel.folderID=d.folder_id  INNER JOIN 
            (SELECT file_id AS fileID,file_name AS fileName,file_note AS fileNote,file_code AS fileCode,file_order AS fileOrder,
            image_url AS imageUrl,file_status AS fileStatus,doc_number AS verNumber,
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id=t.file_id AND create_id={create_id}),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id=t.file_id AND user_id={create_id}),
            isPaid=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.N_OrderCommodityInfo WHERE file_id=t.file_id AND OrderCode IN(
	        SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={create_id} AND OrderState IN(2,4)))
            ,qr_code AS roomPwd,down_number AS downNum,file_money AS filePrice,pag_num AS playerNum,finish_time,
            attach_url AS appID,file_size AS appSize,CONVERT(varchar(100),game_start,120) AS gameStart,
            CONVERT(varchar(100),game_end,120) AS gameEnd,browse_num AS browseNum,vr_star AS starNum,hold_num AS holdNum,
            pay_num AS payNum,CONVERT(varchar(100),update_time,120) AS updateTime,update_name AS updateName,
            CONVERT(varchar(100),create_time,120) AS createTime,create_name AS createName,create_id AS createID
            FROM dbo.T_DocumentInfo t WHERE page_type='QDMetaProgram' AND if_remove=0 AND file_status={param.fileStatus} ");
            //是否推荐
            if (param.isRecommend > -1)
                sb.Append($" AND is_recommend={param.isRecommend} ");
            switch (param.Type)
            {
                case 0: sb.Append($@" AND (create_id={create_id} OR file_id IN(SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={create_id})) "); break;//我创建的+我收藏的(1+2)
                case 1: sb.Append($" AND  create_id={create_id} "); break;//1=我创建的
                case 2: sb.Append($" AND file_id IN(SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={create_id}) "); break;//2=我收藏的
                default:
                    {
                        //创建人id
                        if (user_id > 0)
                            sb.Append($" AND create_id={user_id} ");
                    }
                    break;
            }
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '%{param.keyword}%' OR file_note LIKE '%{param.keyword}%') ");
            sb.Append($")info ON info.fileID = rel.file_id)i  WHERE i.num>{(param.page - 1) * param.rows}");
            return SqlHelper.ExecuteList<OutputMetaResult>(sb.ToString());
        }
        /// <summary>
        ///  获取房间商品列表
        /// </summary>
        /// <returns></returns>
        public List<OutputMetaRoom> GetMetaRoomResult(InputMetaRoomList param, int user_id, int create_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM 
            (SELECT info.*,rel.folderID,rel.folderName,p.*,ROW_NUMBER() OVER(ORDER BY info.updateTime DESC)AS num FROM 
            (SELECT file_id,folder_id AS folderID,(SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=r.folder_id)
            AS folderName FROM dbo.T_DocumentFolderRelation r WHERE 1=1 ");
            if (param.appFileID > 0)
                sb.Append($" AND file_id={param.appFileID} ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" AND folder_path LIKE '{param.folderPath}%' ");
            //Path列表
            if(param.folderPathList!=null && param.folderPathList.Count > 0)
            {
                sb.Append(" AND (");
                for (int i = 0; i < param.folderPathList.Count; i++)
                {
                    //最后一条
                    if (i == param.folderPathList.Count - 1)
                        sb.Append($" folder_path LIKE '{param.folderPathList[i]}%' ");
                    else
                        sb.Append($" folder_path LIKE '{param.folderPathList[i]}%' OR ");
                }
                sb.Append(" ) ");
            }
            sb.Append($@")rel INNER JOIN (SELECT file_id AS appFileID,file_name AS appFileName,attach_url AS appID,
            file_size AS appSize,attach_url,image_url FROM dbo.T_DocumentInfo WHERE 1=1 ");
            if (param.appFileID > 0)
                sb.Append($" AND file_id={param.appFileID} ");
            sb.Append($@")p ON p.appFileID = rel.file_id INNER JOIN 
            (SELECT programId,file_id AS fileID,file_name AS fileName,attach_url as roomID,file_note AS fileNote,file_code AS fileCode,file_order AS fileOrder
            ,qr_code AS roomPwd,down_number AS downNum,file_money AS filePrice,pag_num AS playerNum,finish_time,
            CONVERT(varchar(100),game_start,120) AS gameStart,play_num AS onlineNum,
            CONVERT(varchar(100),game_end,120) AS gameEnd,browse_num AS browseNum,vr_star AS starNum,hold_num AS holdNum,
            pay_num AS payNum,CONVERT(varchar(100),update_time,120) AS updateTime,update_name AS updateName,
            createImage=(SELECT user_img FROM dbo.T_User where user_id=i.create_id),
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id=i.file_id AND create_id={create_id}),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id=i.file_id AND user_id={create_id}),
            isPaid=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.N_OrderCommodityInfo WHERE file_id=i.file_id AND OrderCode IN(
	            SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={create_id} AND OrderState IN(2,4))),
            CONVERT(varchar(100),create_time,120) AS createTime,create_name AS createName,create_id AS createID,play_num,
            attach_url AS attachUrl,image_url AS imageUrl
            FROM dbo.T_DocumentInfo i WHERE page_type='QDMetaRoom' AND if_remove=0  AND file_status=5 ");
            if (param.isUsable == 0)
                sb.Append($" AND game_end<GETDATE() ");
            else if (param.isUsable == 1)
                sb.Append($" AND game_end>GETDATE() ");
            string startDate = string.Empty,
               endDate = string.Empty;
            //开始日期、结束时间
            if (!string.IsNullOrEmpty(param.dateStart))
                startDate = param.dateStart.Substring(0, 4) + "-" + param.dateStart.Substring(4, 2) + "-" + param.dateStart.Substring(6); //DateTime.ParseExact(param.dateStart, "yyyyMMdd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.dateEnd))
                endDate = param.dateEnd.Substring(0, 4) + "-" + param.dateEnd.Substring(4, 2) + "-" + param.dateEnd.Substring(6); // DateTime.ParseExact(param.dateEnd, "yyyyMMdd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(startDate))
                sb.Append($@" AND  CONVERT(varchar(100),game_start, 23) >='{startDate}' ");
            if (!string.IsNullOrEmpty(endDate))
                sb.Append($" AND  CONVERT(varchar(100),game_end, 23)<='{endDate}' ");
            //是否推荐
            if (param.isRecommend > -1)
                sb.Append($" AND is_recommend={param.isRecommend} ");
            switch (param.Type)
            {
                case 0:sb.Append($@" AND (create_id={create_id} OR file_id IN(SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={create_id})) "); break;//我创建的+我收藏的(1+2)
                case 1: sb.Append($@" AND  create_id={create_id} "); break;//1=我创建的
                case 2: sb.Append($@" AND file_id IN(SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={create_id}) "); break;//2=我收藏的
                default: {
                        //创建人id
                        if (user_id > 0)
                            sb.Append($" AND create_id={user_id} ");
                 } break;
            }
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '%{param.keyword}%' OR file_note LIKE '%{param.keyword}%') ");
            sb.Append($@")info ON info.programId = rel.file_id)i WHERE i.num>{(param.page - 1) * param.rows}");
            return SqlHelper.ExecuteList<OutputMetaRoom>(sb.ToString());
        }

        /// <summary>
        ///  获取素材商品列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<MaterialListOutput> GetMaterialList(MaterialListInput param,int user_id, int create_id, out int total)
        {
            sb?.Clear();
            string orderType = " info.createTime ";
            if(param.sortType>0)
                orderType = " info.updateTime ";
            sb.Append($@"SELECT ROW_NUMBER() OVER(ORDER BY {orderType} DESC) AS num,* FROM 
            (SELECT file_id AS fileID,file_name AS fileName,file_code AS fileCode,
            attach_url AS attachUrl,image_url AS imageUrl,CONVERT(varchar(100),issue_start,120)AS  releaseTime,
            file_order AS fileOrder,materType=(SELECT TOP 1 code_name FROM 
            dbo.T_ItemCodeMenum WHERE code_value=i.mater_type),i.file_money AS filePrice,
            i.programId AS attachID,i.file_size AS fileSize,i.browse_num AS browseNum,
            i.vr_star AS starNum,i.hold_num AS holdNum,i.pay_num AS payNum, i.file_note AS fileNote,
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id=i.file_id AND create_id={user_id}),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id=i.file_id AND user_id={user_id}),
            i.down_number AS downNum,CONVERT(varchar(100),i.update_time,120)AS updateTime,
            i.update_name AS updateName,CONVERT(varchar(100),i.create_time,120)AS createTime,
            i.create_name AS createName,createImage=(SELECT user_img FROM dbo.T_User WHERE user_id=i.create_id)
             FROM T_DocumentInfo i WHERE i.page_type='QDMaterial'  ");
            //状态
            if (param.fileStatus > -1)
            {
                if(param.fileStatus==9)
                    sb.Append($" AND i.file_status<>0 ");
                else
                    sb.Append($" AND i.file_status={param.fileStatus} ");
            }
            if (!string.IsNullOrEmpty(param.materType))
                sb.Append($" AND i.mater_type='{param.materType}' ");
            if(param.isRecommend>-1)
                sb.Append($" AND i.is_recommend={param.isRecommend} ");
            if(!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (i.file_name LIKE '%{param.keyword}%' OR i.file_note LIKE '%{param.keyword}%') ");
            if(create_id > 0)
                sb.Append($" AND i.create_id={create_id} ");
            sb.Append($@")info INNER JOIN (SELECT file_id,folder_id AS folderID,folderName=(SELECT folder_name 
            FROM dbo.T_DocumentFolder WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n  WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" AND n.folder_path LIKE '%{param.folderPath}%' ");
            sb.Append(")r ON info.fileID=r.file_id)t ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM ( {sb.ToString()}"));
            return SqlHelper.ExecuteList<MaterialListOutput>($"SELECT TOP {param.rows} * FROM ( "+ sb.ToString() + $" WHERE t.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取用户已购买的素材列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<PaidMaterialOutput> GetPaidMaterialList(PayPaidMaterialInput param, int user_id, out int total)
        {
            sb?.Clear();
            string orderType = " info.createTime ";
            if (param.sortType > 0)
                orderType = " info.updateTime ";
            sb.Append($@"SELECT ROW_NUMBER() OVER(ORDER BY {orderType} DESC) AS num,* FROM 
            (SELECT file_id AS fileID,file_name AS fileName,file_code AS fileCode,
            attach_url AS attachUrl,image_url AS imageUrl,CONVERT(varchar(100),issue_start,120)AS  releaseTime,
            file_order AS fileOrder,materType=(SELECT TOP 1 code_name FROM 
            dbo.T_ItemCodeMenum WHERE code_value=i.mater_type),i.file_money AS filePrice,
            i.programId AS attachID,i.file_size AS fileSize,i.browse_num AS browseNum,
            i.vr_star AS starNum,i.hold_num AS holdNum,i.pay_num AS payNum, i.file_note AS fileNote,
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id=i.file_id AND create_id={user_id}),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id=i.file_id AND user_id={user_id}),
            i.down_number AS downNum,CONVERT(varchar(100),i.update_time,120)AS updateTime,
            i.update_name AS updateName,CONVERT(varchar(100),i.create_time,120)AS createTime,
            i.create_name AS createName,createImage=(SELECT user_img FROM dbo.T_User WHERE user_id=i.create_id)
             FROM T_DocumentInfo i WHERE i.page_type='QDMaterial'  AND EXISTS(SELECT file_id FROM dbo.N_OrderCommodityInfo n WHERE 
	         EXISTS(SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={user_id} AND 
	         OrderState IN(2,4) AND OrderCode=n.OrderCode) AND file_id=i.file_id) ");
            //状态
            if (param.fileStatus > -1)
            {
                if (param.fileStatus == 9)
                    sb.Append($" AND i.file_status<>0 ");
                else
                    sb.Append($" AND i.file_status={param.fileStatus} ");
            }
            if (!string.IsNullOrEmpty(param.materType))
                sb.Append($" AND i.mater_type='{param.materType}' ");
            if (param.isRecommend > -1)
                sb.Append($" AND i.is_recommend={param.isRecommend} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (i.file_name LIKE '%{param.keyword}%' OR i.file_note LIKE '%{param.keyword}%') ");           
            sb.Append($@")info INNER JOIN (SELECT file_id,folder_id AS folderID,folderName=(SELECT folder_name 
            FROM dbo.T_DocumentFolder WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n  WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" AND n.folder_path LIKE '%{param.folderPath}%' ");
            sb.Append(")r ON info.fileID=r.file_id)t ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM ( {sb.ToString()}"));
            return SqlHelper.ExecuteList<PaidMaterialOutput>($"SELECT TOP {param.rows} * FROM ( " + sb.ToString() + $" WHERE t.num>{(param.page - 1) * param.rows}");
        }
    }
}